|
Maximizing Access Database Performance
Most novice users don’t spend any significant time worrying about how well
their database will perform – they focus on the basics of how to design tables, queries,
forms and reports. That is, until users
start entering data and begin to complain that some processes seem very slow. In
this tutorial, we attempt to identify the basic things that should be considered in
tuning a database to improve performance.
Table Design
Good table design can lead to improved performance, as the data being
retrieved is reduced both in size and complexity. By
the same token, bad table design can create serious performance problems and bottlenecks. In
general, a design that is generally third-normal
form will
provide good performance, and is simpler to maintain.
Indexing
Indexes are the most important part of table design from a performance perspective. They
allow Access to quickly find a record or records that meet certain criteria, whether
a query or a filter is being used. On
the other hand, if you are doing lots of data-entry, and your tables have lots of
indexes, the speed with which records can be significantly affected. In
general, all tables should have a primary key that identifies each unique record,
and any foreign keys in a table should be indexed. In
addition, fields that are frequently used for lookup purposes, such as a name field,
should have an index. Finally, you should
be aware that Access by default will apply an index to any integer field whether it
is a primary or foreign key or not. Thus
you may want to check your table designs and turn off indexing when it isn’t necessary.
Query
Design
Good query design and the built-in Rushmore technology that Access inherited
from FoxPro can produce performance that is quite robust, even with a Jet back-end. While
the use of indexes is paramount in achieving good query performance, there are other
factors to be considered as well. In
many cases where a large table is being queried, significant performance boosts can
be achieved by limiting the data being retrieved with a simple query, and then joining
that query with other tables or queries. In
addition, it is common to use SQL strings as the data source for forms and reports,
but these strings have to be compiled each time the form or object is opened, which
can cause some performance degradation. In
such cases, you may want to consider saving the SQL string as a stored query to improve
performance.
Form/Report
Design
Designers often don’t consider the design of forms and reports a performance
issue, but in point of fact, if a form or report is slow to open, the user perceives
it as such. One of the things that can
cause that is extensive use of combo or list boxes. This
can be a major issue if a control has several thousand records to load as its RowSource. If
you find that to be an issue with a design, you should probably rethink the user interface,
and use VBA to do validation of data. Another
thing that can slow performance on the opening of a form is the use of subforms –
the solution we often use is to set the source of the subform object on the main form
when the main form is loaded. Finally,
the splitting of an application into a front-end and back-end, then moving the front-end to
the user computer, can improve form and report load times, especially if users
are running on a 10Mbit LAN.
Client/Server
Design
This approach in its simplest form is nothing more than the split database
noted above, while in more complex forms as it is usually defined, it typically includes
a robust database server application such as Oracle or SQL Server. The
advantage of this approach is that forms and reports are stored on the local workstation
where they load at hard-drive speed, and only data is delivered from the server. (For
more details on the splitting of a database please see our tutorial on
the subject.) However, since the Jet database engine runs on the client workstation,
a complete table must be passed from the server to the workstation in the simple model. The
more complex database server arrangement adds the advantage of returning only the
desired data across the network, as it does all of the query work directly on the
server. This approach can use an ODBC
link, which is the simplest approach, or it can use advanced tools such as Pass-Through
queries, stored procedures, and user defined functions. Using
just the basic client/server design, we have deployed databases with more than 100
users and gotten very acceptable response times. When
circumstances warrant it, you may want to look at the ADP approach, which uses native
SQL Server facilities, and will give you the maximum performance from a SQL Server
database.
General
Issues
There are several things that can cause performance issues that can be resolved
by configuration settings in Access. For
one thing, the Name AutoCorrect feature introduced in Access 2000 can cause delays
when you open a database, so we always turn it off in production deployments. Another
setting is the subdatasheet property for tables – by default they are activated for
Access 2000 and 2002. They have to be
turned off for each table. Finally, regular
compact and repair operations can keep your database running at its best.
Remote
User Support
This can be a significant challenge for developers, as Access presents a sizeable
load for any kind of WAN or dial-up connections. Using
a single database on a server is nearly always unacceptable at dial-up speeds, and
even broadband connections are very sluggish. A
split database is sometimes more acceptable at broadband speeds, but still unacceptable
at dial-up speeds. ODBC connections to
SQL Server may be more acceptable, as the Jet engine translates data requests to TSQL,
and with a good design, will only return small amounts of data, but our experience
indicates that in most situations, users are still unhappy with that arrangement. Two
other approaches bear investigation. One
is the use of replication, which allows a user to only be occasionally connected to
the server, but has a number of other issues that need to be considered – see our tutorial on
that subject. The other approach is the use of remote control software such as PCAnywhere,
or the remote terminal software from Citrix, or Windows Terminal Server on Windows
2000/XP Professional. In essence, these
products transmit screen updates and keystrokes to and from a PC attached to the same
LAN with the database. Such an arrangement
will often give acceptable performance for a remote user connected via the Internet.
Summary
We have found that Access can provide the performance necessary to meet most
situations, given the design is properly constructed and the workstations, server
and LAN facilities are properly configured. For
additional reading, you can consult these resources:
|